const { Client } = require('pg');
const fs = require('fs');
const csv = require('csv-parser');

// 数据库配置
const client = new Client({
    host: "127.0.0.1",
	user: "postgres",
	password: "whuyiyulu666666",
    //此处是你添加的数据库名
	database: "animalsmigration",
	// multipleStatements: true,
	port : 5432,
});

// 处理中文月份转换
const monthMap = {
  '1月': 'January',
  '2月': 'February',
  '3月': 'March',
  '4月': 'April',
  '5月': 'May',
  '6月': 'June',
  '7月': 'July',
  '8月': 'August',
  '9月': 'September',
  '10月': 'October',
  '11月': 'November',
  '12月': 'December'
};

async function importCSV(file_name, speciesName) {
  try {
    await client.connect();
    console.log('Connected to database');

    const results = [];
    
    fs.createReadStream(file_name)
      .pipe(csv())
      .on('data', (data) => {
        // 转换数据格式
        console.log('CSV Columns:', Object.keys(data)); // 检查实际列名
        console.log('event-id value:', data['event-id']); // 检查字段值
        console.log('Raw event-id:', data['event-id']);
        results.push({
        species: speciesName,
          event_id: parseInt(data['event-id']),
          time: monthMap[data.time] || data.time,
          location_long: parseFloat(data['location-long']),
          location_lat: parseFloat(data['location-lat']),
          sensor_type: data['sensor-type'],
          study_name: data['study-name'],
          stage: data['stage'],
          activity: data['activity']
        });
      })
      .on('end', async () => {
        // 批量插入数据
        const query = `
          INSERT INTO animals_track 
          (species, event_id, time, location_long, location_lat, sensor_type, study_name, stage, activity)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`;
        
        for (const row of results) {
        console.log(row);
          await client.query(query, [
            row.species,
            row.event_id,
            row.time,
            row.location_long,
            row.location_lat,
            row.sensor_type,
            row.study_name,
            row.stage,
            row.activity
          ]);
        }
        
        console.log(`${results.length} records inserted`);
        await client.end();
      });
  } catch (err) {
    console.error('Error:', err.stack);
  }
}
speciesName='zebra'
importCSV('./insert_data/data_tobe_inserted/zebra.csv', speciesName);